<?php

class Courses
{
    public $pdo;
    
    public function get_available_to($user)
    {
        $stmt = $this->pdo->prepare("SELECT DISTINCT COURSES.NAME AS NAME,
                                                     COURSES.ID AS ID,
                                                     INSTANCES.ID AS INSTANCE,
                                                     USERS.NAME AS TEACHER
                                     FROM (SELECT * FROM INSTANCES
                                           WHERE START_DATE <= CURRENT_DATE
                                             AND END_DATE   >= CURRENT_DATE
                                             AND ID NOT IN (SELECT DISTINCT INSTANCES.ID
                                                            FROM INSTANCES, GROUPS, REGISTRATIONS
                                                            WHERE REGISTRATIONS.STUDENT = :userid
                                                              AND GROUPS.ID = REGISTRATIONS.GROUPS
                                                              AND INSTANCES.ID = GROUPS.INSTANCE)
                                             AND ID NOT IN (SELECT DISTINCT INSTANCES.ID
                                                            FROM INSTANCES, GROUPS
                                                            WHERE GROUPS.TEACHER = :userid
                                                              AND INSTANCES.ID = GROUPS.INSTANCE)
                                             AND ID NOT IN (SELECT ID FROM INSTANCES WHERE TEACHER = :userid)
                                             AND ID IN (SELECT INSTANCE FROM GROUPS)
                                          ) AS INSTANCES
                                     INNER JOIN COURSES ON(COURSES.ID = INSTANCES.COURSE)
                                     LEFT OUTER JOIN USERS ON(USERS.ID = INSTANCES.TEACHER)
                                     ORDER BY NAME, INSTANCE ASC");
        $stmt->execute(array(":userid"=>$user));
        return $stmt->fetchAll(PDO::FETCH_CLASS);
    }
    
    public function get_taught_by($user)
    {
        $stmt = $this->pdo->prepare("SELECT COURSES.NAME, COURSES.ID AS ID, INSTANCES.ID AS INSTANCE
                                     FROM COURSES, INSTANCES
                                     WHERE INSTANCES.TEACHER = ?
                                       AND COURSES.ID = INSTANCES.COURSE
                                     ORDER BY COURSES.NAME, INSTANCES.ID ASC");
        $stmt->execute(array($user));
        return $stmt->fetchAll(PDO::FETCH_CLASS);
    }
}

?>
